
select co.con_note dati_fimm,
--co.ragf_id,
--ubi.cuf_id,UBI.ATT_ID,
co.CON_ANNO,co.CON_ID,to_char(co.VER_DTINI,'DD/MM/YYYY') data_apertura, to_char(co.VER_DTFINE,'DD/MM/YYYY') data_chiusura,TCON_DES,co.SOG_RAGSOC nome_utente,
sog_pariva,sog_codfis,DECODE_INDID indirizzo_utente,
 decode (rg.SOG_RAGSOC,co.SOG_RAGSOC,'',rg.SOG_RAGSOC) nome_spedizione,
 decode(DECODE_INDIDSPEFAT,replace(DECODE_INDID,'- 05100 ',''),'',DECODE_INDIDSPEFAT) indirizzo_spedizione,
 rg.GF_ID,rg.GF_DES,REGIVA_ID,RAGF_DOM codice_domiciliazione,
rp.BANAPP_DES,rp.RFRP_IBAN,rp.ABICAB_DES,rp.MPAG_DES,
 ATT_IDEXT,DECODE_INDID,agi.TGM_DES ,--TSM_DES,
 agi.TSRC_DES,

  (SELECT
                        ST.SMIS_MAT FROM V_STRMIS  ST
                        WHERE agi.GMIS_ID=st.GMIS_ID
                         and st.STA_IDSYS=2
                                  and st.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                AND ST.TSM_DES NOT LIKE '%REATTIVA%'
                                AND ST.TSM_DES NOT LIKE '%POTENZA%'
                                  AND ST.TSM_DES NOT LIKE '%PRODUZIONE%') misuratore_1,

(select vcsm_val from v_valcxstrmis a, v_strmis st
          WHERE agi.GMIS_ID=st.GMIS_ID
          and st.STA_IDSYS=2
         and st.VER_DTFINE= to_date('31129999','ddmmyyyy')
         and a.STA_IDSYS=2
         and a.VER_DTFINE= to_date('31129999','ddmmyyyy')
         AND ST.TSM_DES NOT LIKE '%REATTIVA%'
          AND ST.TSM_DES NOT LIKE '%POTENZA%'
          AND ST.TSM_DES NOT LIKE '%PRODUZIONE%'
           and a.smis_id=st.smis_id
           and a.csm_id=2) fattore_cor_att,

 (SELECT
ST.SMIS_MAT FROM V_STRMIS  ST
WHERE agi.GMIS_ID=st.GMIS_ID
and st.STA_IDSYS=2
and st.VER_DTFINE= to_date('31129999','ddmmyyyy')
AND ST.TSM_DES LIKE '%REATTIVA%') misuratore_reattiva,

(select vcsm_val from v_valcxstrmis a, v_strmis st
          WHERE agi.GMIS_ID=st.GMIS_ID
         AND ST.TSM_DES LIKE '%REATTIVA%'
                  and st.STA_IDSYS=2
         and st.VER_DTFINE= to_date('31129999','ddmmyyyy')
         and a.STA_IDSYS=2
         and a.VER_DTFINE= to_date('31129999','ddmmyyyy')
           and a.smis_id=st.smis_id
           and a.csm_id=3) fattore_cor_REA,


(SELECT
ST.SMIS_MAT FROM V_STRMIS  ST
WHERE agi.GMIS_ID=st.GMIS_ID
AND ST.TSM_DES LIKE '%POTENZA%') misuratore_POTENZA,

(select vcsm_val from v_valcxstrmis a, v_strmis st
          WHERE agi.GMIS_ID=st.GMIS_ID
         AND ST.TSM_DES LIKE '%POTENZA%'
             and st.STA_IDSYS=2
         and st.VER_DTFINE= to_date('31129999','ddmmyyyy')
         and a.STA_IDSYS=2
         and a.VER_DTFINE= to_date('31129999','ddmmyyyy')     
           and a.smis_id=st.smis_id
           and a.csm_id=4) fattore_cor_POT,
           
(select VCCUF_VAL  from v_valCxcuf UF
                                  where 
                                      uf.CUF_ID=ubi.cuf_id
                                                    
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   CCON_DES ='RESIDENTE') residente,
    (select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                                                    and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='SUPERO_1200000_MM') supero,


  (select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                                                    and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='RDISAGIO_ECON') DISAGIO_ECON,
 (select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                 and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='RAPPL_A_UC') APPL_A_UC,
(select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                                                    and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='DISAGIO_DATA_RINNOVO') DISAGIO_DATA_RINNOVO,
                         (select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                                                    and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='AMMISSIONE_BONUSEE') BONUSEE,
                       (select VPXCO_VAL  from v_valparxcufot ott,v_cufot uf
                                  where ott.CUFOT_VERNUM=uf.VER_NUM
                                  and      uf.CUF_ID=ubi.cuf_id
                                  and VPXCO_FLERR=0
                                                                    and ott.STA_IDSYS=2
                                  and ott.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and uf.STA_IDSYS=2
                                  and uf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                  and   parot_id ='DISAGIO_FISICO') DISAGIO_FISICO,
                                  
                        (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                           and    CCON_DES='TIPO SWITCH'

                                  and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy'))       TIPO_SWITCH,
                                                              
                        (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='MASSIMALE SCONTO')        MASSIMALE_SCONTO,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='QUALIFICA DIPENDENTE')       QUALIFICA_DIPENDENTE ,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='ENTE EROGATORE')        EROGATORE,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='NUMERO DIPENDENTE')        NUMERO_DIPENDENTE,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                           and    CCON_DES='ENTE AMMINISTRATORE')       ENTE_AMMINISTRATORE,
                 (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='QUALIFICA TITOLARE UTENZA')        QUALIFICA_TITOLARE_UTENZA,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                           and    CCON_DES='CLASSE DI CONSUMO PREZZI MEDI')       CLASSE_CONSUMO ,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='MERCATO DI RIFERIMENTO')        MERCATO_RIFERIMENTO,
                               (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                                             and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='CODICE MERCEOLOGICO')        CODICE_MERCEOLOGICO,
                              (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                           and    CCON_DES='PERCENTUALE SCONTO DIPENDENTI')        PERCENTUALE_SCONTO_DIPENDENTI,
                       (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='TIPOLOGIA DI CLIENTE SERVITO ELE')       TIPOLOGIA_CLIENTE_SERVITO_ELE ,
       (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                                          and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='FOTOVOLTAICO')        FOTOVOLTAICO,
              (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='TIPOLOGIA UTENTE')        TIPOLOGIA_UTENTE,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                             and cf.STA_IDSYS=2
                                  and cf.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           and    CCON_DES='APPLICA DEPOSITO CAUZIONALE')        APPLICA_DEPOSITO_CAUZIONALE,
                                                           (select ccon_valvis from v_valcxcuf cf
                                                           where cf.cuf_id=ubi.cuf_id
                                                           and    CCON_DES='QTA KWH SCONTO')       QTA_KWH_SCONTO,
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'POTENZA NOMINALE IMPIANTO DI PRODUZIONE') P_IMP_PROD,
 
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'TENSIONE VOLT') TENSIONE,

                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'TIPO FORNITURA EE') TIPO_FORN,
 
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'POTENZA PUNTO DI SCAMBIO') POT_SCAMB,
                                                          
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'POTENZA DISPONIBILE') POT_DISP,
 
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'POTENZA IMPEGNATA') POT_IMP, 
 
                           (SELECT VCATT_VAL FROM V_VALCXATT  AX
                                                                 where AX.ATT_id=ubi.ATT_id
                                                                 AND AX.AZ_ID='02'
                                                             and AX.STA_IDSYS=2
                                                           and AX.VER_DTFINE= to_date('31129999','ddmmyyyy')
                                                           AND AX.CATT_DES = 'FASI') FASI,
  
(select-- nvl(CONSTDQTA_QTA01,0)+nvl(CONSTDQTA_QTA02,0)+nvl(CONSTDQTA_QTA03,0)+nvl(CONSTDQTA_QTA04,0)+nvl(CONSTDQTA_QTA05,0)+nvl(CONSTDQTA_QTA06,0)+
--nvl(CONSTDQTA_QTA07,0)+nvl(CONSTDQTA_QTA08,0)+nvl(CONSTDQTA_QTA09,0)+nvl(CONSTDQTA_QTA10,0)+nvl(CONSTDQTA_QTA11,0)+nvl(CONSTDQTA_QTA12,0) qta,

CONSTDQTA_QTAANNO qta

 from v_constdannoqta qt,v_cuffrf rf,v_constdanno st
 where qt.FUCO_ID='ATT'
and qt.FACO_ID='FU'
and   rf.cuf_id=ubi.cuf_id
 and rf.CUFFRF_ID=st.CUFFRF_ID
 and st.CONSTD_ID=qt.CONSTD_ID
 and rf.STA_IDSYS=2
 and qt.STA_IDSYS=2
  and st.STA_IDSYS=2
 and rf.FUCO_ID='ATT'
 and rf.az_id='02'
  and qt.az_id='02'
   and st.az_id='02'
 and ser_id ='03') consumi_std_fU,
 
 (select-- nvl(CONSTDQTA_QTA01,0)+nvl(CONSTDQTA_QTA02,0)+nvl(CONSTDQTA_QTA03,0)+nvl(CONSTDQTA_QTA04,0)+nvl(CONSTDQTA_QTA05,0)+nvl(CONSTDQTA_QTA06,0)+
--nvl(CONSTDQTA_QTA07,0)+nvl(CONSTDQTA_QTA08,0)+nvl(CONSTDQTA_QTA09,0)+nvl(CONSTDQTA_QTA10,0)+nvl(CONSTDQTA_QTA11,0)+nvl(CONSTDQTA_QTA12,0) qta,
CONSTDQTA_QTAANNO qta
 from v_constdannoqta qt,v_cuffrf rf,v_constdanno st
 where qt.FUCO_ID='ATT'
and qt.FACO_ID='F1'
and   rf.cuf_id=ubi.cuf_id
 and rf.CUFFRF_ID=st.CUFFRF_ID
 and st.CONSTD_ID=qt.CONSTD_ID
 and rf.STA_IDSYS=2
 and qt.STA_IDSYS=2
  and st.STA_IDSYS=2
 and rf.FUCO_ID='ATT'
 and rf.az_id='02'
  and qt.az_id='02'
   and st.az_id='02'
 and ser_id ='03') consumi_std_f1,
 
 (select-- nvl(CONSTDQTA_QTA01,0)+nvl(CONSTDQTA_QTA02,0)+nvl(CONSTDQTA_QTA03,0)+nvl(CONSTDQTA_QTA04,0)+nvl(CONSTDQTA_QTA05,0)+nvl(CONSTDQTA_QTA06,0)+
--nvl(CONSTDQTA_QTA07,0)+nvl(CONSTDQTA_QTA08,0)+nvl(CONSTDQTA_QTA09,0)+nvl(CONSTDQTA_QTA10,0)+nvl(CONSTDQTA_QTA11,0)+nvl(CONSTDQTA_QTA12,0) qta,
CONSTDQTA_QTAANNO qta
 from v_constdannoqta qt,v_cuffrf rf,v_constdanno st
 where qt.FUCO_ID='ATT'
and qt.FACO_ID='F2'
and   rf.cuf_id=ubi.cuf_id
 and rf.CUFFRF_ID=st.CUFFRF_ID
 and st.CONSTD_ID=qt.CONSTD_ID
 and rf.STA_IDSYS=2
 and qt.STA_IDSYS=2
  and st.STA_IDSYS=2
 and rf.FUCO_ID='ATT'
 and rf.az_id='02'
  and qt.az_id='02'
   and st.az_id='02'
 and ser_id ='03') consumi_std_f2,
 
 (select-- nvl(CONSTDQTA_QTA01,0)+nvl(CONSTDQTA_QTA02,0)+nvl(CONSTDQTA_QTA03,0)+nvl(CONSTDQTA_QTA04,0)+nvl(CONSTDQTA_QTA05,0)+nvl(CONSTDQTA_QTA06,0)+
--nvl(CONSTDQTA_QTA07,0)+nvl(CONSTDQTA_QTA08,0)+nvl(CONSTDQTA_QTA09,0)+nvl(CONSTDQTA_QTA10,0)+nvl(CONSTDQTA_QTA11,0)+nvl(CONSTDQTA_QTA12,0) qta,
CONSTDQTA_QTAANNO qta
 from v_constdannoqta qt,v_cuffrf rf,v_constdanno st
 where qt.FUCO_ID='ATT'
and qt.FACO_ID='F3'
and   rf.cuf_id=ubi.cuf_id
 and rf.CUFFRF_ID=st.CUFFRF_ID
 and st.CONSTD_ID=qt.CONSTD_ID
 and rf.STA_IDSYS=2
 and qt.STA_IDSYS=2
  and st.STA_IDSYS=2
 and rf.FUCO_ID='ATT'
 and rf.az_id='02'
  and qt.az_id='02'
   and st.az_id='02'
 and ser_id ='03') consumi_std_f3
                                                                                                                                                                              
 
                            
 from v_soggetto so,v_conubifrn  ubi,--v_strmis st,
 v_contratto co,v_ragfat rg,v_ragfatregpag rp,v_attgmis agi
where 
 co.con_id=ubi.con_id
 and so.sog_id=co.sog_id
--and to_number(substr(con_note,instr(con_note,'U')+1,instr(con_note,'A')-instr(con_note,'U')-2)) 
--between ^CODICE UTENTE DA xxxxx;N;NUMERICO^^ and ^CODICE UTENTE  A xxxxx;N;NUMERICO^^
and co.con_anno=ubi.con_anno
and agi.STA_IDSYS=2
and agi.VER_DTFINE= to_date('31129999','ddmmyyyy')
and ubi.STA_IDSYS=2
and ubi.VER_DTFINE= to_date('31129999','ddmmyyyy')
and co.STA_IDSYS=2
and co.VER_DTFINE= to_date('31129999','ddmmyyyy')
and rg.STA_IDSYS=2
and rg.VER_DTFINE= to_date('31129999','ddmmyyyy')
and rp.STA_IDSYS=2
and rp.VER_DTFINE= to_date('31129999','ddmmyyyy')
and rg.ragf_id=co.ragf_id
and rg.ragf_id=rp.ragf_id
and agi.ATT_ID=ubi.ATT_ID
--and agi.GMIS_ID=st.GMIS_ID
--and agi.TGM_ID=st.TGM_ID
and rp.sta_idsys= 2
--AND ubi.Cuf_id =91834 con_id =4460 and con_anno =2012
ORDER BY CON_ANNO,CON_ID